package net.kleinhenz.droidshop;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteCursorDriver;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQuery;
import android.util.Log;

import net.kleinhenz.droidshop.model.ListEntry;

public class ListFactory extends SQLiteOpenHelper
{
	public static final String LIST_HISTORY = "history";
	public static final String LIST_WISHLIST = "wishlist";

	private static final String DATABASE_NAME = "DroidShopDB";
	private static final int DATABASE_VERSION = 17;
	
	private Context context = null;
	
	public ListFactory(Context context) 
	{
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}
	
	public static class ListCursor extends SQLiteCursor
	{
		private static final String QUERY = "SELECT listentries.id, keywords, title, type, author, date, imageurl, detailurl " +
				"FROM lists, listentries " +
				"WHERE lists.name = listentries.list AND lists.name = '%NAME%' " + 
				"ORDER BY date DESC";
		public ListCursor(SQLiteDatabase db, SQLiteCursorDriver driver, String editTable, SQLiteQuery query) 
		{
			super(db, driver, editTable, query);
		}
		private static class Factory implements SQLiteDatabase.CursorFactory
		{
			@Override
			public Cursor newCursor(SQLiteDatabase db,
					SQLiteCursorDriver masterQuery, String editTable,
					SQLiteQuery query) 
			{
				return new ListCursor(db, masterQuery, editTable, query);
			}
		}
		public long getColId() { return getLong(getColumnIndexOrThrow("id")); };
		public String getColKeywords() { return getString(getColumnIndexOrThrow("keywords")); };
		public String getColTitle() { return getString(getColumnIndexOrThrow("title")); };
		public String getColType() { return getString(getColumnIndexOrThrow("type")); };
		public String getColAuthor() { return getString(getColumnIndexOrThrow("author")); };
		public String getColImageUrl() { return getString(getColumnIndexOrThrow("imageurl")); };
		public String getColDetailUrl() { return getString(getColumnIndexOrThrow("detailurl")); };
		public Date getColDate() { return new Date(getLong(getColumnIndexOrThrow("date"))); };
		public byte[] getColImage() { return getBlob(getColumnIndexOrThrow("date")); };
	}

	public List<ListEntry> retrieveList(String name)
	{	
		List<ListEntry> result = new ArrayList<ListEntry>();
		
		ListCursor c = this.getList(name);
		for (int row=0; row<c.getCount(); row++)
		{
			c.moveToPosition(row);
			ListEntry thisEntry = new ListEntry();
			result.add(thisEntry);
			thisEntry.setId(c.getColId());
			thisEntry.setAuthor(c.getColAuthor());
			thisEntry.setDate(c.getColDate());
			thisEntry.setKeywords(c.getColKeywords());
			thisEntry.setTitle(c.getColTitle());
			thisEntry.setImageUrl(c.getColImageUrl());
			thisEntry.setDetailUrl(c.getColDetailUrl());
			thisEntry.setType(c.getColType());
		}
		closeList(c);
		
		return result;
	}

	public void addToList(String listName, ListEntry entry)
	{
		ContentValues map = new ContentValues();
		map.put("author", entry.getAuthor());
		map.put("keywords", entry.getKeywords());
		map.put("title", entry.getTitle());
		map.put("type", entry.getType());
		map.put("imageurl", entry.getImageUrl());
		map.put("detailurl", entry.getDetailUrl());
		map.put("date", entry.getDate().getTime());
		map.put("list", listName);
		SQLiteDatabase db = getWritableDatabase();
		try
		{
			entry.setId(db.insert("listentries", null, map));
		}
		catch (SQLException e)
		{
			Log.e(DroidShop.APP_NAME, "Error inserting data into entry list: " + e.getMessage());
			throw new RuntimeException(e);
			// FIXME: signal user
		}
		finally
		{
			if (db!=null)
				db.close();
		}
	}
	
	public void removeFromList(ListEntry entry)
	{
		String[] whereArgs = new String[] {Long.toString(entry.getId())};
		SQLiteDatabase db = getWritableDatabase();
		try
		{
			db.delete("listentries", "id=?", whereArgs);
		}
		catch (SQLException e)
		{
			Log.e(DroidShop.APP_NAME, "Error deleting data from list: " + e.getMessage());
			throw new RuntimeException(e);
			// FIXME: signal user
		}
		finally
		{
			if (db!=null)
				db.close();
		}
	}
	
	public void removeAllFromList(String name)
	{
		String[] whereArgs = new String[] {name};
		SQLiteDatabase db = getWritableDatabase();
		try
		{
			db.delete("listentries", "list=?", whereArgs);
		}
		catch (SQLException e)
		{
			Log.e(DroidShop.APP_NAME, "Error deleting all data from list: " + e.getMessage());
			throw new RuntimeException(e);
			// FIXME: signal user
		}
		finally
		{
			if (db!=null)
				db.close();
		}
	}

	private ListCursor getList(String name)
	{
		String sql = ListCursor.QUERY.replace("%NAME%", name);
		SQLiteDatabase db = getReadableDatabase();
		ListCursor c = (ListCursor)db.rawQueryWithFactory(new ListCursor.Factory(), sql, null, null);
		c.moveToFirst();
		return c;
	}
	
	private void closeList(ListCursor c)
	{
		SQLiteDatabase db = c.getDatabase();
		c.close();
		db.close();
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) 
	{
		String[] sql = context.getString(R.string.sql_create).split("\n");
		db.beginTransaction();
		try
		{
			execSQLStatements(db, sql);
			db.setTransactionSuccessful();
		}
		catch (SQLException e)
		{
			Log.e(DroidShop.APP_NAME, "Error creating database: " + e.getMessage());
			// FIXME: proper signal to user
			throw new RuntimeException(e);
		}
		finally
		{
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
	{
		String[] sql = context.getString(R.string.sql_drop).split("\n");
		db.beginTransaction();
		try
		{
			execSQLStatements(db, sql);
			db.setTransactionSuccessful();
		}
		catch (SQLException e)
		{
			Log.e(DroidShop.APP_NAME, "Error creating database: " + e.getMessage());
			// FIXME: proper signal to user
			throw new RuntimeException(e);
		}
		finally
		{
			db.endTransaction();
		}		
		onCreate(db);
	}

	private void execSQLStatements(SQLiteDatabase db, String[] sql)
	{
		for (String statement : sql)
			if (statement.trim().length()>0)
				db.execSQL(statement);
	}
}
